package com.mainbo.modular.excel.impl;

import cn.hutool.core.io.resource.ClassPathResource;
import cn.stylefeng.roses.core.util.HttpContext;
import com.baomidou.mybatisplus.mapper.EntityWrapper;
import com.mainbo.core.util.ExcelCascadeRecord;
import com.mainbo.core.util.ExcelUtils;
import com.mainbo.core.util.ExcelUtils.ExcelType;
import com.mainbo.core.util.StringUtil;
import com.mainbo.modular.excel.ClassUserBatchTeacherService;
import com.mainbo.modular.excel.ExcelBatchService;
import com.mainbo.modular.jms.JmsService;
import com.mainbo.modular.jms.MessageTypes;
import com.mainbo.modular.jms.model.ClassUserMessage;
import com.mainbo.modular.system.dao.PtAreaMapper;
import com.mainbo.modular.system.dao.PtClassMapper;
import com.mainbo.modular.system.dao.PtOrgMapper;
import com.mainbo.modular.system.model.*;
import com.mainbo.modular.system.model.meta.MetaUtils;
import com.mainbo.modular.system.model.meta.vo.Meta;
import com.mainbo.modular.system.service.IPtAccountService;
import com.mainbo.modular.system.service.IPtClassUserService;
import com.mainbo.modular.system.service.IPtUserRoleService;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.util.*;

/**
 * @author moshang
 * @date 2020-03-08
 **/
@Service
public class ClassUserBatchTeacherServiceImpl extends ExcelBatchService
        implements ClassUserBatchTeacherService {

    private static final Logger logger = LoggerFactory
            .getLogger(ClassUserBatchTeacherServiceImpl.class);

    @Resource
    private PtOrgMapper orgMapper;
    @Resource
    private PtAreaMapper areaMapper;
    @Resource
    private PtClassMapper classMapper;
    @Resource
    private IPtUserRoleService userRoleService;
    @Resource
    private IPtAccountService accountService;
    @Resource
    private IPtClassUserService classUserService;

    @Resource
    private JmsService jmsService;

    private static final String ORG_ID = "orgId";
    /**
     * 用户模板头部所在行
     */
    private final Integer headline = 3;

    /**
     * 下载班级的任课教师批量关联模板
     *
     * @param orgId
     *          机构ID
     * @param schoolYear
     *          学年ID
     */

    @SuppressWarnings("unused")
    @Override
    public void downloadBatchManageTeacherTemplate(String orgId,
                                                   HttpServletResponse response, Integer schoolYear) throws IOException {
        Workbook workbook = null;
        String fileName = "批量设置" + schoolYear + "学年任课教师";
        String filepath = "";
        filepath = "/registertemplate/batchmanagetemplate_teacher.xls";
        ClassPathResource resource = new ClassPathResource(filepath);
        InputStream inputStream = resource.getStream();
        workbook = ExcelUtils.createWorkBook(inputStream,ExcelType.EXL_2003);
        if (workbook == null) {
            returnErrMsg(filepath, response);
            return;
        }
        Sheet sheet = workbook.getSheet("Sheet");
        PtOrg org = orgMapper.selectById(orgId);
        String orgStr = "学校名称：" + org.getName();
        PtArea area1 = areaMapper.selectById(org.getAreaId());
        if (area1.getPid().intValue() != 0) {
            PtArea area2 = areaMapper.selectById(area1.getPid());
            orgStr = "省(市)：" + area2.getAreaName() + "     区(县)："
                    + area1.getAreaName() + "     " + orgStr;
        } else {
            orgStr = "区(县)：" + area1.getAreaName() + "     " + orgStr;
        }
        sheet.getRow(2).getCell(0).setCellValue(orgStr);
        Map<String, Object> params = new HashMap<String, Object>();

        params.put(TITLE_PARAMS, new HashSet<ExcelTitle>());
        params.put("schoolYear", schoolYear);

        List<String> subjectNameList = getAllSubjectNameList(org, params);// 获取该学校所有学科的名称合集
        int startColumn = 2;// 从第三列开始动态加载学科
        for (String subjectName : subjectNameList) {
            sheet.getRow(headline).createCell(startColumn)
                    .setCellStyle(sheet.getRow(headline).getCell(1).getCellStyle());// 给新添列添加样式
            sheet.getRow(headline).getCell(startColumn).setCellValue(subjectName);
            sheet.autoSizeColumn(startColumn);// 自动调整列宽
            startColumn++;
        }
        this.loadSheetStyle(sheet, startColumn);// 设置单元格格式
        Map<ExcelTitle, Column> headers = parseExcelHeader(sheet, params);
        initSheetTeplate(workbook, sheet, org, headers, params);
        try {
            if (workbook != null) {
                String ext = filepath.substring(filepath.lastIndexOf("."));
                response.reset();
                response.setContentType("application/x-msdownload");
                response.setHeader("Content-Disposition", "attachment; filename="
                        + new String(fileName.getBytes("gb2312"), "ISO-8859-1") + ext);
                workbook.write(response.getOutputStream());
            } else {
                response.getWriter().write("模板文件不存在！");
            }
        } catch (Exception e) {
            logger.error("", e);

        } finally {
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (IOException e) {
                    logger.error("close failed", e);
                }
            }
        }

    }

    private List<String> getAllSubjectNameList(PtOrg org,
                                               Map<String, Object> params) {
        Map<String, Meta> subjectMap = getAllSubjectMetaList(org.getId());
        Set<String> subjectNameSet = subjectMap.keySet();
        addTitle(ExcelHeader.GRADE, params);
        addTitle(ExcelHeader.CLASS, params);
        for (String subjectName : subjectNameSet) {
            addTitle(new ExcelHeader(subjectName, false), params);
        }
        return sortSubjectName(subjectMap);
    }

    private List<String> sortSubjectName(Map<String, Meta> subjectMap) {
        List<String> sortedSn = new ArrayList<String>();
        List<Meta> subs = new ArrayList<>(subjectMap.values());
        Collections.sort(subs, new Comparator<Meta>() {
            @Override
            public int compare(Meta o1, Meta o2) {
                return o1.getId() > o2.getId() ? 1 : -1;
            }
        });

        for (Meta mt : subs) {
            sortedSn.add(mt.getName());
        }

        return sortedSn;
    }

    private void loadSheetStyle(Sheet sheet, int startColumn) {
        CellRangeAddress cra0 = new CellRangeAddress(0, 0, 0, startColumn - 1);
        CellRangeAddress cra1 = new CellRangeAddress(1, 1, 0, startColumn - 1);
        CellRangeAddress cra2 = new CellRangeAddress(2, 2, 0, startColumn - 1);
        // 根据列数合并前三行单元格
        sheet.addMergedRegion(cra0);
        sheet.addMergedRegion(cra1);
        sheet.addMergedRegion(cra2);
        CellStyle cellStyle = sheet.getRow(0).getCell(0).getCellStyle();
        cellStyle.setAlignment(CellStyle.ALIGN_LEFT); // 居左
        CellStyle cellStyle1 = sheet.getRow(1).getCell(0).getCellStyle();
        cellStyle1.setAlignment(CellStyle.ALIGN_CENTER); // 居中
        CellStyle cellStyle2 = sheet.getRow(2).getCell(0).getCellStyle();
        cellStyle2.setAlignment(CellStyle.ALIGN_CENTER); // 居中
    }

    private void returnErrMsg(String path, HttpServletResponse response) {
        response.setHeader("Content-type", "text/html;charset=UTF-8");
        StringBuilder rb = new StringBuilder(path).append(" 不存在!");
        try {
            response.getWriter().write(rb.toString());
        } catch (IOException e) {
            logger.error("", e);
        }
    }

    /**
     * 初始化学校模板
     */
    @SuppressWarnings("unchecked")
    private void initSheetTeplate(Workbook workbook, Sheet sheet,
                                  PtOrg org, Map<ExcelTitle, Column> headers,
                                  Map<String, Object> params) {
        Column cn = null;
        List<Meta> gradeMetas = getAllgradeMetaList(org);
        Map<Integer, String> gradeMap = new HashMap<Integer, String>();// 年级id name 映射
        Map<String, Object> nameMap = new LinkedHashMap<String, Object>();// 年级 下所有班级映射
        for (Meta meta : gradeMetas) {
            gradeMap.put(meta.getId(), meta.getName());
            nameMap.put(meta.getName(), new ArrayList<String>());
        }
        Integer gradeClumnIndex = null;
        if ((cn = headers.get(map(ExcelHeader.GRADE.getName(), params))) != null) {
            gradeClumnIndex = cn.getIndex();
        }
        if ((cn = headers.get(map(ExcelHeader.CLASS.getName(), params))) != null) {
            int clumnIndex = cn.getIndex();
            EntityWrapper<PtClass> classModel = new EntityWrapper<>();
            classModel.eq("org_id",org.getId());
            classModel.eq("enable",1);
            classModel.eq("school_year",(Integer) params.get("schoolYear"));
            classModel.orderBy(" sort ",true);
            List<PtClass> classList = classMapper.selectList(classModel);
            String[] classnames = new String[classList.size() + 1];
            int i = 0;
            for (PtClass sch : classList) {
                if (gradeMap.get(sch.getGradeId()) != null) {
                    List<String> clss = (List<String>) nameMap
                            .get(gradeMap.get(sch.getGradeId()));
                    if (clss != null) {
                        clss.add(sch.getName());
                    } else {
                        List<String> names = new ArrayList<String>();
                        names.add(sch.getName());
                        nameMap.put(gradeMap.get(sch.getGradeId()), names);
                    }
                }
                classnames[i++] = sch.getName();
            }
            classnames[i] = "无";
            if (gradeClumnIndex != null) {
                ExcelCascadeRecord createCascadeRecord = ExcelUtils
                        .createCascadeRecord(workbook, nameMap);// 创建数据字典第一行
                DataValidation setDataValidationName = ExcelUtils.createDataValidation(
                        sheet, createCascadeRecord.getName(), headline + 1, 500,
                        gradeClumnIndex, gradeClumnIndex);
                sheet.addValidationData(setDataValidationName);
                for (int start = headline + 1; start < 500; start++) {
                    int relationRow = start + 1;
                    DataValidation setDataValidationByName = ExcelUtils
                            .createDataValidation(sheet,
                                    "INDIRECT($" + ExcelUtils.indexToColumn(gradeClumnIndex + 1)
                                            + "$" + relationRow + ")",
                                    start, start, clumnIndex, clumnIndex);
                    sheet.addValidationData(setDataValidationByName);
                }

            }
        }
    }

    /**
     * 获取学校的所有班级
     */
    private List<Meta> getAllgradeMetaList(PtOrg org) {
        List<Meta> phaseList = MetaUtils.getOrgTypeProvider()
                .listAllPhase(org.getSchoolings());
        Map<Integer, List<Meta>> phaseGradeMap = MetaUtils.getOrgTypeProvider()
                .listPhaseXzGradeMap(org.getSchoolings());
        List<Meta> gradeMetaList = new ArrayList<Meta>();
        for (Meta phaseMeta : phaseList) {
            gradeMetaList.addAll(phaseGradeMap.get(phaseMeta.getId()));
        }
        return gradeMetaList;
    }

    /**
     * 批量导入班级与任课教师的关联
     */
    @Override
    public StringBuilder batchImportTeacher(String orgId, MultipartFile file,
                                            Integer schoolYear) {
        Map<String, Object> params = new HashMap<String, Object>();
        StringBuilder resultStr = new StringBuilder(); // 反馈信息
        params.put(ORG_ID, orgId);
        params.put("schoolYear", schoolYear);
        try {
            ClassUserMessage classUserVo = new ClassUserMessage();
            classUserVo.setOrgId(orgId);
            classUserVo.setType(PtClassUser.TYPE_TEACHER);
            classUserVo.setStartTime(new Date());
            classUserVo.setMessageType(MessageTypes.CLASS_TEACHER_IMPORT.name());
            classUserVo.setSchoolYear(schoolYear);
            resultStr.append("<strong>==》开始导入数据，请等待...</strong><br>");
            this.importData(file.getInputStream(),
                    ExcelType.parseExcelType(file.getOriginalFilename()), params,
                    resultStr);
            resultStr.append("<br><strong>《==数据导入结束。</strong>");
//            LoggerUtils.insertLogger(LoggerModule.ORGMANAGE,
//                    "组织机构管理——班级管理——批量设置任课教师，学校ID:{}", orgId);
            classUserVo.setEndTime(new Date());
            try {
                @SuppressWarnings("unchecked")
                Set<String> classIds = (Set<String>) params.get("classIds");
                if (classIds != null && classIds.size() > 0) {
                    classUserVo.setClassIds(classIds);
                    classUserVo.setMessageType(MessageTypes.CLASS_TEACHER_IMPORT.name());
                    jmsService.sendMessage(classUserVo);
                }
            } catch (Exception e) {
                logger.error("组织机构管理——班级管理——批量导入任课教师——消息发送失败", e);
            }
        } catch (Exception e) {
            resultStr.append("批量导入失败：").append(e.getMessage());
            logger.error("read excel file failed", e);
        }

        return resultStr;
    }

    static final class ExcelHeader implements ExcelTitle {

        private final boolean required;
        private final int size;
        private final String name;

        public static final ExcelHeader GRADE = new ExcelHeader("年级", false);
        public static final ExcelHeader CLASS = new ExcelHeader("班级", false);

        private ExcelHeader(final String name, final boolean required,
                            final int size) {
            this.required = required;
            this.size = size;
            this.name = name;
        }

        private ExcelHeader(final String name, final boolean required) {
            this.required = required;
            this.size = Integer.MAX_VALUE;
            this.name = name;
        }

        @Override
        public List<String> getMapNames() {
            return Arrays.asList(getName());
        }

        @Override
        public boolean isRequired() {
            return required;
        }

        public int getSize() {
            return size;
        }

        public String getName() {
            return name;
        }

        @Override
        public int length() {
            return this.size;
        }

    }

    @Override
    protected void beforeSheetParse(Sheet sheet, Map<String, Object> params,
                                    StringBuilder returnMsg) {
        if (params != null) {
            // 年级班级匹配
            List<Meta> gradeMetaList = getAllGradeMetaList(
                    String.valueOf(params.get(ORG_ID)));
            Map<Integer, Map<String, String>> gradeClassMaps = new HashMap<Integer, Map<String, String>>();
            params.put("classIds", new HashSet<String>());
            Map<String, Integer> gradeMetaMap = new HashMap<String, Integer>();
            for (Meta gradeMeta : gradeMetaList) {
                EntityWrapper<PtClass> wrapper = new EntityWrapper<>();
                wrapper.eq("grade_id",gradeMeta.getId());
                wrapper.eq("org_id",String.valueOf(params.get(ORG_ID)));
                wrapper.eq("enable",1);
                List<PtClass> classList = classMapper.selectList(wrapper);
                Map<String, String> map = new HashMap<String, String>();
                for (PtClass classInfo : classList) {
                    map.put(classInfo.getId(), classInfo.getName());
                }
                gradeClassMaps.put(gradeMeta.getId(), map);
                gradeMetaMap.put(gradeMeta.getName(), gradeMeta.getId());
            }
            params.put("gradeClass", gradeClassMaps);

            Map<String, Meta> subjectMetaList = getAllSubjectMetaList(
                    String.valueOf(params.get(ORG_ID)));
            addTitle(ExcelHeader.GRADE, params);
            addTitle(ExcelHeader.CLASS, params);
            for (String key : subjectMetaList.keySet()) {
                Meta meta = subjectMetaList.get(key);
                addTitle(new ExcelHeader(meta.getName(), false), params);
            }

            params.put("subjectMetaList", subjectMetaList);
            params.put("gradeMetaList", gradeMetaList);
            params.put("gradeMetaMap", gradeMetaMap);
            params.put("lookUpUser", lookUpUser(String.valueOf(params.get(ORG_ID))));
        }

    }

    @SuppressWarnings("unchecked")
    protected ExcelTitle map(String name, Map<String, Object> params) {
        Set<ExcelTitle> titles = (Set<ExcelTitle>) params.get(TITLE_PARAMS);
        for (ExcelTitle title : titles) {
            if (title != null && title.getMapNames().contains(name)) {
                return title;
            }
        }

        return null;
    }

    @SuppressWarnings("unchecked")
    @Override
    protected void parseRow(Map<ExcelTitle, String> rowValueMap,
                            Map<String, Object> params, Row row, StringBuilder sb) {
        if (row != null) {
            int i = row.getRowNum();
            Map<String, Integer> gradeMetaMap = (Map<String, Integer>) params
                    .get("gradeMetaMap");
            Map<Integer, Map<String, String>> lookUpUser = (Map<Integer, Map<String, String>>) params
                    .get("lookUpUser");
            Set<String> clssIds = (Set<String>) params.get("classIds");
            // 数据校验
            if (!checkCulumn(i, rowValueMap, sb, params, gradeMetaMap)) {
                return;
            }
            // 数据保存
            ClassUserMessage scVo = new ClassUserMessage();
            String classId = getClassId(rowValueMap, params);
            scVo.setClassId(classId);
            scVo.setOrgId(String.valueOf(params.get(ORG_ID)));
            if (classId == null) {
                sb.append("第" + (i + 1) + "行班级不存在，忽略此行数据。<br>");
                logger.debug("第{}行班级不存在，忽略此行数据。", i + 1);
                return;
            }
            // 其他教师数据
            List<PtClassUser> teacherList = new ArrayList<PtClassUser>();
            Map<String, Meta> subjectMetaList = (Map<String, Meta>) params
                    .get("subjectMetaList");
            for (Meta meta : subjectMetaList.values()) {
                String teacherName = rowValueMap.get(map(meta.getName(), params));
                Integer subjectId = meta.getId();
                PtClassUser teacher = new PtClassUser();
                boolean hasTeacher = false;
                if (lookUpUser.get(subjectId) != null) {
                    String accountId = lookUpUser.get(subjectId).get(teacherName);
                    if (StringUtils.isNotBlank(accountId)) {
                        PtAccount account = accountService.selectById(accountId);
                        if (account != null) {
                            teacher.setSubjectId(subjectId);
                            teacher.setUserName(teacherName);
                            teacher.setAccount(account.getAccount());
                            teacher.setUserId(accountId);
                            teacher.setSchoolYear((Integer) params.get("schoolYear"));
                            teacherList.add(teacher);
                            hasTeacher = true;
                        }
                    }
                }

                if (!hasTeacher) {
                    sb.append("警告： 第").append(i + 1).append("行学科‘").append(meta.getName())
                            .append("’教师不存在。<br>");
                }
            }
            if (teacherList.size() > 0) {
                scVo.setClassUserList(teacherList);
                scVo.setSchoolYear((Integer) params.get("schoolYear"));
                classUserService.saveOrUpdateClassUser(scVo, PtClassUser.TYPE_TEACHER);
                clssIds.add(scVo.getClassId());
            }
        }
    }

    private boolean checkCulumn(int i, Map<ExcelTitle, String> rowValueMap,
                                StringBuilder sb, Map<String, Object> params,
                                Map<String, Integer> gradeMetaMap) {
        i++;
        // 校验空数据
        String gradeName = rowValueMap
                .get(map(ExcelHeader.GRADE.getName(), params));
        String className = rowValueMap
                .get(map(ExcelHeader.CLASS.getName(), params));
        if (StringUtils.isEmpty(gradeName) || StringUtils.isEmpty(className)) {
            sb.append("第" + (i) + "行必填项有空值，忽略此行数据。<br>");
            logger.debug("第{}行必填项有空值，忽略此行数据。", i);
            return false;
        }
        // 校验年级班级是否对应
        @SuppressWarnings("unchecked")
        Map<Integer, Map<String, String>> gradeClassMap = (Map<Integer, Map<String, String>>) params
                .get("gradeClass");
        Map<String, String> classMap = gradeClassMap
                .get(gradeMetaMap.get(gradeName));
        if (classMap != null && !classMap.containsValue(className)) {
            sb.append("第" + (i) + "行年级下不存在相应的班级，忽略此行数据。<br>");
            logger.debug("第" + (i) + "行年级下不存在相应的班级，忽略此行数据。", i);
            return false;
        }
        return true;
    }

    private Map<Integer, Map<String, String>> lookUpUser(String orgId) {
        EntityWrapper<PtUserRole> entityWrapper=new EntityWrapper<>();
        entityWrapper.eq("org_id",orgId);
        entityWrapper.eq("enable",1);
        entityWrapper.andNew().eq("sys_role_id",SysRole.TEACHER.getId()).
                or().eq("sys_role_id",SysRole.SCHMANAGER.getId()).
                or().eq("sys_role_id",SysRole.SCHWORKER.getId());
        List<PtUserRole> uslist = userRoleService.selectList(entityWrapper);
        Map<Integer, Map<String, String>> subjectuserMap = new HashMap<Integer, Map<String, String>>();
        Map<String, String> u = null;
        for (PtUserRole us : uslist) {
            if (us.getSubjectId() == null || us.getSubjectId().intValue() == 0) {
                continue;
            }
            u = subjectuserMap.get(us.getSubjectId());
            if (u != null) {
                u.put(us.getName(), us.getAccountId());
            } else {
                u = new HashMap<String, String>();
                u.put(us.getName(), us.getAccountId());
                subjectuserMap.put(us.getSubjectId(), u);
            }
        }
        return subjectuserMap;
    }

    @SuppressWarnings("unchecked")
    private String getClassId(Map<ExcelTitle, String> rowValueMap,
                              Map<String, Object> params) {
        String gradeName = rowValueMap
                .get(map(ExcelHeader.GRADE.getName(), params));
        String className = rowValueMap
                .get(map(ExcelHeader.CLASS.getName(), params));
        Map<Integer, Map<String, String>> gradeClassMap = (Map<Integer, Map<String, String>>) params
                .get("gradeClass");
        Map<String, Integer> greNames = (Map<String, Integer>) params
                .get("gradeMetaMap");
        Map<String, String> classMap = gradeClassMap.get(greNames.get(gradeName));// 当前年级对应的所有班级
        for (String classId : classMap.keySet()) {
            if (classMap.get(classId).equals(className)) {
                return classId;
            }
        }
        return null;
    }

    private Map<String, Meta> getAllSubjectMetaList(String orgId) {
        PtOrg org = orgMapper.selectById(orgId);
        List<Meta> phaseList = MetaUtils.getOrgTypeProvider()
                .listAllPhase(org.getSchoolings());
        Map<String, Meta> subjectMetaList = new TreeMap<>();
        for (Meta phase : phaseList) {
            List<Meta> subjectList = MetaUtils.getPhaseSubjectProvider()
                    .listAllSubject(org.getId(), phase.getId(),
                            StringUtil.toIntegerArray(
                                    org.getAreaIds().substring(1, org.getAreaIds().length() - 1),
                                    ","));
            for (Meta meta : subjectList) {
                subjectMetaList.put(meta.getName(), meta);
            }
        }
        return subjectMetaList;
    }

    /**
     * 获取学校下所有的年级集合
     */
    private List<Meta> getAllGradeMetaList(String orgId) {
        PtOrg org = orgMapper.selectById(orgId);

        // 学校的学段-年级map
        Map<Integer, List<Meta>> phasegradeMap = MetaUtils.getOrgTypeProvider()
                .listPhaseXzGradeMap(org.getSchoolings());
        List<Meta> gradeMetaList = new ArrayList<Meta>();
        Collection<List<Meta>> gradeListList = phasegradeMap.values();
        for (List<Meta> gradeListTemp : gradeListList) {
            gradeMetaList.addAll(gradeListTemp);
        }
        return gradeMetaList;
    }

    @Override
    public int sheetIndex() {
        return 0;
    }

    @Override
    public int titleLine() {
        return headline;
    }

    /**
     * 处理标题内容，去掉空白字符，去掉* 号 子类覆盖做特殊处理
     *
     */
    @Override
    protected String prepareTitle(String name) {
        name = super.prepareTitle(name);
        return name != null && name.length() > 4 ? name.replaceAll("\\(必填\\)", "")
                : name;
    }

    /**
     * 判定模板是否设置标题列为必填
     *
     */
    @Override
    protected boolean isRequiredColumn(String columnName) {
        if (columnName != null) {
            return columnName.contains("必填");
        }
        return super.isRequiredColumn(columnName);
    }

    @Override
    protected ExcelTitle[] titles() {
        return null;
    }

}
